# 作者:小宇
# 2025年11月12日14时46分27秒
# 1225074067@qq.com
import pandas as pd
import json
import re
from pathlib import Path
import os

# 首先查看上传的Excel文件结构
file_path = './data/13文化 - 总数据和各朝代数据.xlsx'

# 查看Excel文件的所有sheet
excel_file = pd.ExcelFile(file_path)
print("Excel文件包含的工作表:")
for i, sheet in enumerate(excel_file.sheet_names, 1):
    print(f"{i}. {sheet}")

# 查看每个sheet的数据结构
print("\n" + "="*50)
for sheet_name in excel_file.sheet_names:
    print(f"\n工作表: {sheet_name}")
    try:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        print(f"数据形状: {df.shape}")
        print(f"列名: {list(df.columns)}")
        print("前5行数据:")
        print(df.head())
        print("数据类型:")
        print(df.dtypes)
    except Exception as e:
        print(f"读取工作表 {sheet_name} 时出错: {e}")
    print("-"*30)


def extract_cultural_info_from_text(text):
    """从文化文本中提取关键信息"""
    if pd.isna(text) or not isinstance(text, str):
        return {
            'year': None,
            'institutions': None,  # 文化机构
            'art_forms': None,  # 艺术形式
            'books': None,  # 书籍文献
            'events': None  # 文化事件
        }

    # 提取年份信息（更全面的模式）
    year_patterns = [
        r'公元\s*(\d{3,4})\s*年',
        r'(\d{3,4})\s*年',
        r'公元前\s*(\d{3,4})\s*年',
        r'(\d{3,4})\s*-\s*(\d{3,4})\s*年',  # 年份范围
        r'(\d{1,2})\s*世纪\s*(\d{1,2})\s*年代'  # 世纪年代
    ]

    year = None
    for pattern in year_patterns:
        match = re.search(pattern, text)
        if match:
            year = match.group(0)  # 保留原始年份表述
            break

    # 提取文化机构（学校、书院、出版社等）
    institution_patterns = [
        r'[太国子]学|书院|学堂|学校|学院|翰林院|出版社|书局|馆舍|寺庙|道观|教堂'
    ]
    institutions = []
    for pattern in institution_patterns:
        matches = re.findall(pattern, text)
        if matches:
            institutions.extend(matches)
    institutions = list(set(institutions)) if institutions else None  # 去重

    # 提取艺术形式（戏剧、文学、绘画等）
    art_patterns = [
        r'戏剧|戏曲|京剧|昆曲|说书|弹词|琵琶|杂技|绘画|书法|音乐|舞蹈|文学|诗歌|小说|散文'
    ]
    art_forms = []
    for pattern in art_patterns:
        matches = re.findall(pattern, text)
        if matches:
            art_forms.extend(matches)
    art_forms = list(set(art_forms)) if art_forms else None  # 去重

    # 提取书籍文献
    book_patterns = [
        r'《([^》]+)》',  # 书名号中的书籍
        r'著有《([^》]+)》',
        r'编撰《([^》]+)》',
        r'编写《([^》]+)》'
    ]
    books = []
    for pattern in book_patterns:
        matches = re.findall(pattern, text)
        if matches:
            books.extend(matches)
    books = list(set(books)) if books else None  # 去重

    # 提取文化事件
    event_patterns = [
        r'发现|发掘|建立|创建|设立|举办|召开|出版|发表|创作|完成|修缮|重建'
    ]
    events = []
    for pattern in event_patterns:
        # 查找包含事件关键词的短语
        event_matches = re.findall(r'([^。，；！？]*?' + pattern + '[^。，；！？]*?)[。，；！？]', text)
        if event_matches:
            events.extend([match.strip() for match in event_matches if len(match.strip()) > 5])
    events = list(set(events)) if events else None  # 去重

    return {
        'year': year,
        'institutions': institutions,
        'art_forms': art_forms,
        'books': books,
        'events': events
    }


def extract_data_from_cultural_excel(file_path, sheet_names=None):
    """从文化Excel文件中提取数据"""
    try:
        # 如果没有指定sheet名称，则读取所有sheet
        if sheet_names is None:
            excel_file = pd.ExcelFile(file_path)
            sheet_names = excel_file.sheet_names

        all_results = []

        for sheet_name in sheet_names:
            print(f"正在处理工作表: {sheet_name}")

            # 读取Excel工作表
            df = pd.read_excel(file_path, sheet_name=sheet_name)

            # 清理数据：去除第一行（如果是标题行）
            if not pd.isna(df.iloc[0, 0]) and str(df.iloc[0, 0]) == '时期':
                df = df.iloc[1:].reset_index(drop=True)

            # 处理每一行数据
            for idx, row in df.iterrows():
                # 获取时期（朝代）信息
                period = row.iloc[0] if not pd.isna(row.iloc[0]) else None
                # 获取文化文本信息
                cultural_text = row.iloc[1] if len(row) > 1 and not pd.isna(row.iloc[1]) else None
                # 获取出处信息
                source = row.iloc[2] if len(row) > 2 and not pd.isna(row.iloc[2]) else None

                # 只有当时期和文化文本都存在时才处理
                if period and cultural_text:
                    # 从文化文本中提取关键信息
                    extracted_info = extract_cultural_info_from_text(str(cultural_text))

                    # 创建数据记录
                    record = {
                        'category': str(period).strip(),  # 时期/朝代
                        'sheet_name': sheet_name,  # 来源工作表
                        'cultural_text': str(cultural_text).strip()[:500] + '...' if len(
                            str(cultural_text)) > 500 else str(cultural_text).strip(),  # 文化文本（截断长文本）
                        'full_text_length': len(str(cultural_text)),  # 完整文本长度
                        'year': extracted_info['year'],
                        'institutions': extracted_info['institutions'],
                        'art_forms': extracted_info['art_forms'],
                        'books': extracted_info['books'],
                        'events': extracted_info['events'],
                        'source': source  # 文献出处
                    }

                    all_results.append(record)

        return all_results

    except Exception as e:
        print(f"读取文件时出错: {e}")
        import traceback
        traceback.print_exc()
        return []


def save_cultural_data_to_json(data, output_file):
    """将文化数据保存为JSON文件"""
    try:
        # 创建输出目录（如果不存在）
        output_dir = Path(output_file).parent
        output_dir.mkdir(parents=True, exist_ok=True)

        # 保存为JSON文件，确保中文正常显示
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(data, f, ensure_ascii=False, indent=2)

        print(f"数据已成功保存到: {output_file}")
        return True

    except Exception as e:
        print(f"保存文件时出错: {e}")
        return False


def analyze_cultural_data(data):
    """分析文化数据的基本统计信息"""
    if not data:
        print("没有数据可分析")
        return

    print("\n" + "=" * 60)
    print("文化数据统计分析报告")
    print("=" * 60)

    # 1. 基本统计
    print(f"\n1. 基本统计:")
    print(f"   - 总记录数: {len(data)}")

    # 2. 时期/朝代分布
    categories = {}
    for record in data:
        category = record['category']
        if category in categories:
            categories[category] += 1
        else:
            categories[category] = 1

    print(f"\n2. 时期/朝代分布:")
    for category, count in sorted(categories.items(), key=lambda x: x[1], reverse=True):
        print(f"   - {category}: {count} 条记录")

    # 3. 各工作表数据分布
    sheet_distribution = {}
    for record in data:
        sheet = record['sheet_name']
        if sheet in sheet_distribution:
            sheet_distribution[sheet] += 1
        else:
            sheet_distribution[sheet] = 1

    print(f"\n3. 各工作表数据分布:")
    for sheet, count in sorted(sheet_distribution.items()):
        print(f"   - {sheet}: {count} 条记录")

    # 4. 信息提取统计
    year_count = 0
    institution_count = 0
    art_count = 0
    book_count = 0
    event_count = 0

    for record in data:
        if record['year']:
            year_count += 1
        if record['institutions']:
            institution_count += 1
        if record['art_forms']:
            art_count += 1
        if record['books']:
            book_count += 1
        if record['events']:
            event_count += 1

    print(f"\n4. 信息提取统计:")
    print(f"   - 包含年份信息: {year_count} 条 ({year_count / len(data) * 100:.1f}%)")
    print(f"   - 包含文化机构: {institution_count} 条 ({institution_count / len(data) * 100:.1f}%)")
    print(f"   - 包含艺术形式: {art_count} 条 ({art_count / len(data) * 100:.1f}%)")
    print(f"   - 包含书籍文献: {book_count} 条 ({book_count / len(data) * 100:.1f}%)")
    print(f"   - 包含文化事件: {event_count} 条 ({event_count / len(data) * 100:.1f}%)")

    # 5. 文本长度统计
    text_lengths = [record['full_text_length'] for record in data]
    avg_length = sum(text_lengths) / len(text_lengths)
    max_length = max(text_lengths)
    min_length = min(text_lengths)

    print(f"\n5. 文本长度统计:")
    print(f"   - 平均长度: {avg_length:.0f} 字符")
    print(f"   - 最长文本: {max_length} 字符")
    print(f"   - 最短文本: {min_length} 字符")

    print("\n" + "=" * 60)


# 主函数
def main():
    # 配置参数
    input_file = './data/13文化 - 总数据和各朝代数据.xlsx'
    output_file = './outputs/cultural_data.json'

    print("开始处理文化数据...")
    print(f"输入文件: {input_file}")

    # 1. 提取数据
    print("\n步骤1: 从Excel文件中提取文化数据")
    cultural_data = extract_data_from_cultural_excel(input_file)

    if not cultural_data:
        print("提取数据失败，程序终止")
        return

    # 2. 分析数据
    print("\n步骤2: 分析提取的文化数据")
    analyze_cultural_data(cultural_data)

    # 3. 保存数据
    print("\n步骤3: 保存数据到JSON文件")
    save_success = save_cultural_data_to_json(cultural_data, output_file)

    if save_success:
        # 显示前几条数据作为示例
        print("\n前3条数据示例:")
        for i, record in enumerate(cultural_data[:3], 1):
            print(f"\n示例 {i}:")
            print(f"  时期(category): {record['category']}")
            print(f"  来源工作表: {record['sheet_name']}")
            print(f"  年份: {record['year'] if record['year'] else '未提取到'}")
            print(f"  文化机构: {', '.join(record['institutions']) if record['institutions'] else '未提取到'}")
            print(f"  艺术形式: {', '.join(record['art_forms']) if record['art_forms'] else '未提取到'}")
            print(f"  书籍文献: {', '.join(record['books']) if record['books'] else '未提取到'}")
            print(f"  文化事件: {len(record['events']) if record['events'] else 0} 个" + (
                f" (示例: {record['events'][0][:50]}...)" if record['events'] and len(
                    record['events'][0]) > 50 else ""))
            print(f"  文本预览: {record['cultural_text'][:100]}..." if len(
                record['cultural_text']) > 100 else f"  文本: {record['cultural_text']}")
            print(f"  出处: {record['source'][:100]}..." if record['source'] and len(
                str(record['source'])) > 100 else f"  出处: {record['source']}")
            print("-" * 80)

    print(f"\n数据处理完成！共处理 {len(cultural_data)} 条文化记录。")
    print(f"完整数据已保存至: {output_file}")

    return cultural_data


# 运行主程序
if __name__ == '__main__':
    cultural_data = main()